# -*- coding: utf-8 -*-
import pymysql
import csv
import codecs
import oss2
from datetime import date, timedelta


def uploadOss(yourObjectName, yourLocalFile):
    auth = oss2.Auth('LTAI4G5Qj5RWmADie7tu8vh5', '2VSjtTngJA0ey1REklxzPA0TQH2UEB')
    bucket = oss2.Bucket(auth, 'oss-cn-shanghai.aliyuncs.com', 'jt-report')
    bucket.put_object_from_file(yourObjectName, yourLocalFile)


def get_conn():
    conn = pymysql.connect(host='10.33.130.110', port=9030, user='root', passwd='wNkpD2MtrESn', db='jms_dwd',
                           charset='utf8')
    return conn


def query_all(cur, sql, args):
    cur.execute(sql, args)
    return cur.fetchall()


def read_mysql_to_csv(filename, sql, fieldnames):
    with codecs.open(filename=filename, mode='w', encoding='utf-8') as f:
        writer = csv.DictWriter(f, fieldnames=fieldnames)
        writer.writeheader()
        conn = get_conn()
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        results = query_all(cur=cur, sql=sql, args=None)
        for result in results:
            writer.writerow(result)


def main():
    yesterday = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d")
    # 揽收量
    fieldnames = ['日期', "代理区", "加盟商", "网点名称", "网点编码", "类型", "0-1", "1-2", "2-3", "3-4", "4-5", "5-6", "6-7", "7-8",
                  "8-9", "9-10", "10-11", "11-12", "12-13", "13-14", "14-15", "15-16", "16-17", "17-18", "18-19",
                  "19-20", "20-21", "21-22", "22-23", "23-24"]
    sql = f"""select taking_date `日期`,
       start_subordinate_agent_name `代理区`,
	   start_parent_network_name `加盟商`,
	   start_pick_network_name `网点名称`,
	   start_pick_network_code `网点编码`,
			 type `类型`,
       sum(case when hours='00' then 1 else 0 end) as `0-1`,
			 sum(case when hours='01' then 1 else 0 end) as `1-2`,
			 sum(case when hours='02' then 1 else 0 end) as `2-3`,
			 sum(case when hours='03' then 1 else 0 end) as `3-4`,
			 sum(case when hours='04' then 1 else 0 end) as `4-5`,
			 sum(case when hours='05' then 1 else 0 end) as `5-6`,
			 sum(case when hours='06' then 1 else 0 end) as `6-7`,
			 sum(case when hours='07' then 1 else 0 end) as `7-8`,
			 sum(case when hours='08' then 1 else 0 end) as `8-9`,
			 sum(case when hours='09' then 1 else 0 end) as `9-10`,
			 sum(case when hours='10' then 1 else 0 end) as `10-11`,
			 sum(case when hours='11' then 1 else 0 end) as `11-12`,
			 sum(case when hours='12' then 1 else 0 end) as `12-13`,
			 sum(case when hours='13' then 1 else 0 end) as `13-14`,
			 sum(case when hours='14' then 1 else 0 end) as `14-15`,
			 sum(case when hours='15' then 1 else 0 end) as `15-16`,
			 sum(case when hours='16' then 1 else 0 end) as `16-17`,
			 sum(case when hours='17' then 1 else 0 end) as `17-18`,
			 sum(case when hours='18' then 1 else 0 end) as `18-19`,
			 sum(case when hours='19' then 1 else 0 end) as `19-20`,
			 sum(case when hours='20' then 1 else 0 end) as `20-21`,
			 sum(case when hours='21' then 1 else 0 end) as `21-22`,
			 sum(case when hours='22' then 1 else 0 end) as `22-23`,
			 sum(case when hours='23' then 1 else 0 end) as `23-24`
from (
select taking_date,
       start_subordinate_agent_name,
	   start_parent_network_name,
	   start_pick_network_name,
	   start_pick_network_code,
			 '揽收量' type,
			 case when taking_time is not null then DATE_FORMAT(taking_time,'%H')
			      when taking_time is null then DATE_FORMAT(warehous_time,'%H') end as hours
 from jms_dwd.dwd_waybill_prescription_reach_details where taking_date='{yesterday}'
)t group by taking_date,
            start_subordinate_agent_name,
			start_parent_network_name,
	   start_pick_network_name,
	   start_pick_network_code,
					  type
   order by taking_date asc limit 100000""".format(yesterday=yesterday)
    read_mysql_to_csv(f'/tmp/揽收量_{yesterday}.csv'.format(today=yesterday), sql, fieldnames)
    yourObjectName = f'揽收量_时效签收量/揽收量_{yesterday}.csv'.format(today=yesterday)  # oos 目的文件
    yourLocalFile = f'/tmp/揽收量_{yesterday}.csv'.format(today=yesterday)  # 本地文件
    uploadOss(yourObjectName, yourLocalFile)

    # 时效签收量
    fieldnames = ['日期', "代理区", "加盟商", "网点名称", "网点编码", "类型", "0-1", "1-2", "2-3", "3-4", "4-5", "5-6", "6-7", "7-8",
                  "8-9", "9-10", "10-11", "11-12", "12-13", "13-14", "14-15", "15-16", "16-17", "17-18", "18-19",
                  "19-20", "20-21", "21-22", "22-23", "23-24"]
    sql = f"""select taking_date `日期`,
       sign_subordinate_agent_name `代理区`,
	   sign_parent_network_name `加盟商`,
	   sign_network_name `网点名称`,
	   sign_network_code `网点编码`,
			 type `类型`,
       sum(case when hours='00' then 1 else 0 end) as `0-1`,
			 sum(case when hours='01' then 1 else 0 end) as `1-2`,
			 sum(case when hours='02' then 1 else 0 end) as `2-3`,
			 sum(case when hours='03' then 1 else 0 end) as `3-4`,
			 sum(case when hours='04' then 1 else 0 end) as `4-5`,
			 sum(case when hours='05' then 1 else 0 end) as `5-6`,
			 sum(case when hours='06' then 1 else 0 end) as `6-7`,
			 sum(case when hours='07' then 1 else 0 end) as `7-8`,
			 sum(case when hours='08' then 1 else 0 end) as `8-9`,
			 sum(case when hours='09' then 1 else 0 end) as `9-10`,
			 sum(case when hours='10' then 1 else 0 end) as `10-11`,
			 sum(case when hours='11' then 1 else 0 end) as `11-12`,
			 sum(case when hours='12' then 1 else 0 end) as `12-13`,
			 sum(case when hours='13' then 1 else 0 end) as `13-14`,
			 sum(case when hours='14' then 1 else 0 end) as `14-15`,
			 sum(case when hours='15' then 1 else 0 end) as `15-16`,
			 sum(case when hours='16' then 1 else 0 end) as `16-17`,
			 sum(case when hours='17' then 1 else 0 end) as `17-18`,
			 sum(case when hours='18' then 1 else 0 end) as `18-19`,
			 sum(case when hours='19' then 1 else 0 end) as `19-20`,
			 sum(case when hours='20' then 1 else 0 end) as `20-21`,
			 sum(case when hours='21' then 1 else 0 end) as `21-22`,
			 sum(case when hours='22' then 1 else 0 end) as `22-23`,
			 sum(case when hours='23' then 1 else 0 end) as `23-24`
from (
select DATE_FORMAT(pres_sign_time,'%Y-%m-%d') as taking_date,
       sign_subordinate_agent_name,
	   sign_parent_network_name,
	   sign_network_name,
	   sign_network_code,
	   '时效签收量' type,
	   DATE_FORMAT(pres_sign_time,'%H') as hours
 from jms_dwd.dwd_waybill_prescription_reach_details where DATE_FORMAT(pres_sign_time,'%Y-%m-%d')='{yesterday}' and sign_subordinate_agent_name is not null
)t group by taking_date,
            sign_subordinate_agent_name,
			sign_parent_network_name,
	        sign_network_name,
	        sign_network_code,
					  type
   order by taking_date asc limit 100000""".format(yesterday=yesterday)
    read_mysql_to_csv(f'/tmp/时效签收量_{yesterday}.csv'.format(today=yesterday), sql, fieldnames)
    yourObjectName = f'揽收量_时效签收量/时效签收量_{yesterday}.csv'.format(today=yesterday)  # oos 目的文件
    yourLocalFile = f'/tmp/时效签收量_{yesterday}.csv'.format(today=yesterday)  # 本地文件
    uploadOss(yourObjectName, yourLocalFile)


if __name__ == '__main__':
    main()
